package com.sparrow.orm;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;

import javax.sql.DataSource;

import com.sparrow.constant.*;
import com.sparrow.core.Log;
import com.sparrow.datasource.ConnectionPool;
import com.sparrow.enums.STATUS_RECORD;
import com.sparrow.support.DefinedJson;
import com.sparrow.utils.StringUtil;

public class DBHelper {

	/************************************** 变量定义 ********************************************************************/

	private static Map<String, DBHelper> dbHelperPool = new ConcurrentHashMap<String, DBHelper>();

	/**
	 * 单例模式的实例
	 */
	private static DBHelper instance = new DBHelper();

	/**
	 * 链接池对象
	 */
	private DataSource connectionPool;

	/**
	 * 单例模式 的对外对象获取
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @return
	 */
	public static DBHelper getInstance() {
		instance.connectionPool = ConnectionPool.getInstance();
		return instance;
	}

	public static DBHelper getInstance(Enum<?> pn) {
		String poolName = pn.toString().toLowerCase();
		if (dbHelperPool.containsKey(poolName)) {
			return dbHelperPool.get(poolName);
		} else {
			DBHelper dbHelper = new DBHelper();
			dbHelper.connectionPool = ConnectionPool.getInstance(pn);
			dbHelperPool.put(poolName, dbHelper);
			return dbHelper;
		}
	}

	/************************************* 执行基础SQL调用 参数与存储过程 ***************************************************/

	public DataSource getConnectionPool() {
		return connectionPool;
	}

	/**
	 * 设置参数
	 * 
	 * 参数类型未知的情况下调用
	 * 
	 * 仅提供给ORM 新增更新时，ORM反射未知数据类型时使用
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param preparedStatement
	 * @param parameter
	 * @param index
	 * @throws SQLException
	 */
	private void bindParameter(PreparedStatement preparedStatement,
			SQLParameter parameter, int index) {
		Class<?> fieldType = parameter.getParameterType();
		Object value = parameter.getParameterValue();
		try {
			if (fieldType == int.class) {
				preparedStatement.setInt(
						index,
						StringUtil.isNullOrEmpty(value) ? 0 : Integer
								.valueOf(value.toString()));
			} else if (fieldType == long.class) {
				preparedStatement.setLong(
						index,
						StringUtil.isNullOrEmpty(value) ? 0 : Long
								.valueOf(value.toString()));
			} else if (fieldType == String.class) {
				preparedStatement.setString(index, StringUtil
						.isNullOrEmpty(value) ? "" : String.valueOf(value));
			} else if (fieldType == Date.class) {
				preparedStatement
						.setDate(
								index,
								StringUtil.isNullOrEmpty(value) ? COMMON.MIN_BEIJING_UNIX_DATE_FOR_MYSQL
										: Date.valueOf(value.toString()));
			} else if (fieldType == Timestamp.class) {
				preparedStatement
						.setTimestamp(
								index,
								StringUtil.isNullOrEmpty(value) ? COMMON.MIN_BEIJING_UNIX_TIMESTAMP_FOR_MYSQL
										: Timestamp.valueOf(value.toString()));
			} else if (fieldType == boolean.class) {
				preparedStatement.setBoolean(
						index,
						StringUtil.isNullOrEmpty(value) ? false : value
								.equals(String.valueOf(STATUS_RECORD.ENABLE
										.ordinal())) ? true : Boolean
								.valueOf(value.toString()));
			} else if (fieldType == double.class) {
				preparedStatement.setDouble(
						index,
						StringUtil.isNullOrEmpty(value) ? 0.0 : Double
								.valueOf(value.toString()));
			} else {
				Log.getInstance().info(
						"DBHelper setSQLParameter error sqlType not exist"
								+ fieldType);
			}
		} catch (Exception e) {
			Log.getInstance().error(
					e,
					"DBHelper setSQLParameter error field:"
							+ parameter.getParameterField() + " value:" + value
							+ " type:" + fieldType);
		}
	}

	/**
	 * 获取PreparedStatement对象用于参数化SQL的执行
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * 
	 *            命令SQL
	 * @param parameters
	 *            参数
	 * @param connection
	 *            当前链接对象
	 * @param isIncInsert
	 *            是否自增的新建记录命令
	 * @return
	 * @throws Exception
	 */
	private PreparedStatement getPreparedStatement(String commandString,
			SQLParameter[] parameters, Connection connection,
			boolean isIncInsert) {
		PreparedStatement prestmt = null;
		// 是否为事务
		boolean isACID = true;
		try {
			if (connection == null) {
				connection = this.connectionPool.getConnection();
			}
			// 非自动提交即是事务
			isACID = !connection.getAutoCommit();
			if (isIncInsert) {
				prestmt = connection.prepareStatement(commandString,
						Statement.RETURN_GENERATED_KEYS);
			} else {
				// 存储过程
				if (commandString.trim().toLowerCase().startsWith("call")) {
					prestmt = connection.prepareCall(commandString);
				} else {
					prestmt = connection.prepareStatement(commandString);
				}
			}
			for (int i = 0; i < parameters.length; i++) {
				this.bindParameter(prestmt, parameters[i], i + 1);
			}
			return prestmt;
		} catch (Exception e) {
			// 如果非事务则释放链接
			if (isACID) {
				try {
					connection.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			if (prestmt != null) {
				prestmt = null;
			}
			Log.getInstance().info(
					"Dbherper getPreparedStatement Exception|" + e.toString());
			return null;
		} finally {
			for (int i = 0; i < parameters.length; i++) {
				Object parameterValue = parameters[i].getParameterValue();
				if (parameterValue == null) {
					parameterValue = COMMON.EMPTY;
				}
				commandString = commandString.replaceFirst("\\?",
						Matcher.quoteReplacement(parameterValue.toString()));
			}
			System.out.println(commandString);
		}
	}

	/*************************************** 执行更新操作(增删改) ***********************************************************/
	/**
	 * 执行多条更新语句
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @throws Exception
	 */
	public void executeUpdate(String[] commandString) throws Exception {
		Statement statement = ConnectionPool.getInstance().getConnection()
				.createStatement();
		for (int i = 0; i < commandString.length; i++) {
			statement.addBatch(commandString[i]);
		}
		try {
			statement.executeBatch();
		} finally {
			statement.getConnection().close();
			statement.close();
		}
	}

	/**
	 * 执行一条非参数化的更新语句.
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @throws Exception
	 */
	public int executeUpdate(String commandString) throws Exception {
		Connection connection = ConnectionPool.getInstance().getConnection();
		Statement statement = connection.createStatement();
		try {
			return statement.executeUpdate(commandString);
		} finally {
			connection.close();
			statement.close();
		}
	}

	/**
	 * 执行一条非参数化的更新语句（用于事务)
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @param connection
	 * @throws Exception
	 */
	public int executeUpdate(String commandString, Connection connection)
			throws Exception {
		if (connection == null) {
			return this.executeUpdate(commandString);
		} else {
			return connection.createStatement().executeUpdate(commandString);
		}
	}

	/**
	 * 执行一条参数化的更新语句
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-4
	 * @version 1.0
	 * @param commandString
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public int executeUpdate(String commandString, SQLParameter[] parameters)
			throws Exception {
		PreparedStatement preparedStatement = this.getPreparedStatement(
				commandString, parameters, null, false);
		if (preparedStatement != null) {
			try {
				return preparedStatement.executeUpdate();
			} finally {
				preparedStatement.getConnection().close();
				preparedStatement.close();
			}
		}
		return 0;
	}

	/**
	 * 执行一条参数化的更新语句 (用于事务)
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-4
	 * @version 1.0
	 * @param commandString
	 * @param parameters
	 * @param connection
	 * @return
	 * @throws Exception
	 */
	public int executeUpdate(String commandString, SQLParameter[] parameters,
			Connection connection) throws Exception {
		if (connection == null) {
			return this.executeUpdate(commandString, parameters);
		} else {
			PreparedStatement preparedStatement = this.getPreparedStatement(
					commandString, parameters, connection, false);
			return preparedStatement.executeUpdate();
		}
	}

	/**
	 * 执行自增插入 (非事务)
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public int executeAutoIncrementInsert(String commandString,
			SQLParameter[] parameters) throws Exception {
		int generatedKey = 0;
		PreparedStatement preparedStatement = this.getPreparedStatement(
				commandString, parameters, null, true);
		try {
			preparedStatement.executeUpdate();
			ResultSet result = preparedStatement.getGeneratedKeys();
			if (result.next()) {
				generatedKey = result.getInt(1);
			}
		} finally {
			// 非事务时释放链接
			preparedStatement.getConnection().close();
			preparedStatement.close();
		}
		return generatedKey;
	}

	/**
	 * 执行自增插入（事务)
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @param parameters
	 * @param connection
	 * @return
	 * @throws Exception
	 */
	public int executeAutoIncrementInsert(String commandString,
			SQLParameter[] parameters, Connection connection) throws Exception {
		if (connection == null) {
			return this.executeAutoIncrementInsert(commandString, parameters);
		}
		int generatedKey = 0;
		PreparedStatement preparedStatement = this.getPreparedStatement(
				commandString, parameters, connection, true);
		preparedStatement.executeUpdate();
		ResultSet result = preparedStatement.getGeneratedKeys();
		if (result.next()) {
			generatedKey = result.getInt(1);
		}
		return generatedKey;
	}

	/******************************************************* 返回结果集 ********************************************/
	/**
	 * 执行一条SELECT语句 不关闭链接
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @param helper
	 * @return
	 * @throws Exception
	 */
	public ResultSet executeQuery(String commandString,
			SQLParameter[] parameters) {
		Log.getInstance().info("executeResultSet SQL:" + commandString);
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			connection = this.connectionPool.getConnection();
			if (parameters == null || parameters.length == 0) {
				statement = connection.createStatement();
				rs = statement.executeQuery(commandString);
			} else {
				statement = this.getPreparedStatement(commandString,
						parameters, connection, false);
				rs = ((PreparedStatement) statement).executeQuery();
			}

			if (rs == null) {
				connection.close();
				if (statement != null) {
					statement.close();
				}
				return null;
			} else {
				return rs;
			}
		} catch (Exception e) {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e1) {
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e1) {
				}
			}
			try {
				connection.close();
			} catch (SQLException e1) {
			}
			Log.getInstance().error(e);
			return null;
		}
	}

	public ResultSet executeQuery(String commandString) {
		return this.executeQuery(commandString, null);
	}

	/**
	 * 执行SELECT语句返回一行一列
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param commandString
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public Object executeScalar(String commandString, SQLParameter[] parameters) {
		Object result = null;
		ResultSet rs = this.executeQuery(commandString, parameters);
		if (rs != null) {
			try {
				if (rs.next()) {
					result = rs.getObject(1);
				}
			} catch (Exception e) {
				Log.getInstance().error(e);
				result = null;
			} finally {
				this.release(rs);
			}
			return result;
		} else {
			return null;
		}
	}

	public Object executeScalar(String commandString) {
		return this.executeScalar(commandString, null);
	}

	public void release(ResultSet rs) {
		try {
			if (rs != null) {
				Statement statement = rs.getStatement();
				Connection connection = statement.getConnection();
				connection.close();
				statement.close();
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 将ResultSet返回json格式
	 * 
	 * @author zhanglizhi
	 * @date 2012-4-7
	 * @version 1.0
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public String getJSONByResultSet(ResultSet rs) throws SQLException {
		return this.getJSONByResultSet(rs, null);
	}

	/**
	 * 将ResultSet返回json格式
	 * 
	 * @author zhanglizhi
	 * @date 2012-5-21
	 * @version 1.0
	 * @param rs
	 * @param idefJson
	 * @return
	 * @throws SQLException
	 */
	public String getJSONByResultSet(ResultSet rs, DefinedJson idefJson)
			throws SQLException {
		if (rs == null)
			return COMMON.NULL_JSON;
		else {
			ResultSetMetaData rsmd = rs.getMetaData();
			StringBuffer resultJson = new StringBuffer();
			String strFieldValue = "";
			while (rs.next()) {
				resultJson.append("{");
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					strFieldValue = rs.getString(i);
					strFieldValue = StringUtil.getSafeJSONString(strFieldValue);
					resultJson.append(rsmd.getColumnLabel(i) + ":'"
							+ strFieldValue + "',");
				}
				if (idefJson != null) {
					resultJson.append(idefJson.getItem(rs));
				} else {
					resultJson.deleteCharAt(resultJson.lastIndexOf(","));
				}
				resultJson.append("},");
			}
			this.release(rs);
			if (resultJson.length() != 0) {
				return "["
						+ resultJson.deleteCharAt(resultJson.length() - 1)
								.toString() + "]";

			} else {
				return COMMON.NULL_JSON;
			}
		}
	}
}
